﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.SqlClient;

namespace HomeDMSObjectModel
{
    public class HomeDMSContext : DataContext
    {
        public HomeDMSContext(string fileName) : base(fileName)
        {
        }
        
        public Tag CreateTag(string name)
        {
            Tag t = new Tag();
            t.Name = name;
            tags.InsertOnSubmit(t);
            SubmitChanges();
            return t;
        }

        public Table<Document> documents;
        public Table<Page> pages;
        public Table<Tag> tags;
        public Table<DocumentTagAssociation> documentTags;

        public void AddTagToDocument(Document doc, Tag tag)
        {
            int count = documentTags.Count<DocumentTagAssociation>(assoc => assoc.TagId == tag.TagId && assoc.DocumentId == doc.DocumentId);
            if (count == 0)
            {
                DocumentTagAssociation assoc = new DocumentTagAssociation();
                assoc.Document = doc;
                assoc.Tag = tag;
                documentTags.InsertOnSubmit(assoc);
            }
        }

        public void DeleteTagAssociations(Tag tag)
        {
            var q = from assoc in documentTags where assoc.TagId == tag.TagId select assoc;

            documentTags.DeleteAllOnSubmit<DocumentTagAssociation>(q);
        }

        public void DeleteDocumentAssociations(Document doc)
        {
            var q = from assoc in documentTags where assoc.DocumentId == doc.DocumentId select assoc;

            documentTags.DeleteAllOnSubmit<DocumentTagAssociation>(q);
        }

        public IList<Document> FindDocumentByName(string name)
        {
            var q = from doc in documents where doc.Name.Contains(name) select doc;
            return new List<Document>(q);
        }

        public IList<Document> FindDocumentByTag(Tag tag)
        {
            var q = from dta in documentTags where dta.Tag == tag select dta.Document;
            return new List<Document>(q);
        }

        public bool DeleteDocument(Document doc)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("delete from Document_Tag where document_id = {0};", doc.DocumentId);
            sql.AppendFormat("delete from Pages where DocumentId = {0};", doc.DocumentId);
            sql.AppendFormat("delete from Document where document_id = {0}",doc.DocumentId);
            SqlConnection con = ((SqlConnection)this.Connection);
            con.Open();
            using (SqlTransaction trans = con.BeginTransaction())
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sql.ToString(), con);
                    cmd.Transaction = trans;
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                    return true;
                }
                catch (Exception)
                {
                    trans.Rollback();
                    return false;                    
                }
                finally
                {
                    con.Close();
                }
            }
            
        }

        public void PrefillDatabaseWithData()
        {
            CreateTag("receipt");
            CreateTag("check");
            CreateTag("paycheck");
            CreateTag("invoice");
            CreateTag("docket");
            CreateTag("fine");
            CreateTag("letter");
            CreateTag("photo");
            CreateTag("picture");
            CreateTag("will");
            CreateTag("note");
            CreateTag("book");
            CreateTag("bill");
            CreateTag("lecture");
            CreateTag("contract");
            CreateTag("insurance");
            CreateTag("card");
            CreateTag("recepie");
            CreateTag("mobile");
            CreateTag("phone");
            CreateTag("electricity");
            CreateTag("municipality");
            CreateTag("tax");
            CreateTag("tv");
            CreateTag("cable");
            CreateTag("water");
            CreateTag("sewer");
            CreateTag("medical");
            CreateTag("bank");
            CreateTag("gas");
            CreateTag("creditcard");
            CreateTag("internet");
        }
    }
}
